﻿using SIG.DAL.Dapper.Model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIG.DAL.Dapper.Repositories
{
    public class AlbumRepository
    {
        private DbConnection _connection;

        public Album GetById(int id)
        {
            Album result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Get<Album>(id);
            }
            return result;
        }
        public void Update(Album model)
        {         
            using (_connection = Utilities.GetOpenConnection())
            {
                _connection.Update(model);
            }           
        }
        public IEnumerable<Album> GetActiveAlbums()
        {
            IEnumerable<Album> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Album>(new { Active = true });
            }
            return result;
        }

        public Album GetItemBySeoName(string seoName)
        {
            Album result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Album>(new { SeoName = seoName }).FirstOrDefault();
            }
            return result;
        }

        public bool IsExistSeoName(string seoName)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                seoName = seoName.ToLower();
                result = _connection.RecordCount<Album>("Where SeoName = @SeoName", new { SeoName = seoName });
            }
            return result > 0;

        }
        public bool IsExistSeoName(string seoName, int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                seoName = seoName.ToLower();
                result = _connection.RecordCount<Album>("Where SeoName = @SeoName and Id <> @Id", new { SeoName = seoName, Id = id });
            }
            return result > 0;
        }

        public int? Create(Album model)
        {
            int? result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Insert(model);
            }
            return result;
        }

   
        public int Delete(Album model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete(model);
            }
            return result;
        }

        public int Delete(int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete<Album>(id);
            }
            return result;
        }

        public Album GetPrev(int id)
        {
            Album result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Album>( "where Active = 1 and Id < @Id", new { Id = id })
                    .OrderByDescending(m=>m.Id).FirstOrDefault();
            }
            return result;
        }
        public Album GetNext(int id)
        {
            Album result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Album>("where Active = 1 and Id > @Id", new { Id = id })
                    .OrderByDescending(m => m.Id).FirstOrDefault();
            }
            return result;
        }

        public IEnumerable<Album> GetListPagedAcitveAlbums(int pageIndex, int pageSize)
        {
            IEnumerable<Album> result;
            using (_connection = Utilities.GetOpenConnection())
            {               
                result = _connection.GetListPaged<Album>(pageIndex, pageSize, "where Active=1", "CreatedDate desc");
            }
            return result;
        }

        public IEnumerable<Album> GetListPagedAcitveAlbums(int pageIndex, int pageSize, out int totalCount)
        {
            IEnumerable<Album> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                totalCount = _connection.RecordCount<Album>("where Active=1");
                result = _connection.GetListPaged<Album>(pageIndex, pageSize, "where Active=1", "CreatedDate desc");
            }
            return result;
        }

        public IEnumerable<Album> GetListPagedItems(int pageIndex, int pageSize, string keyword, out int totalCount)
        {
            IEnumerable<Album> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                totalCount = _connection.RecordCount<Album>("where Title like @Title", new { Title = likeKeyword });
                result = _connection.GetListPaged<Album>(pageIndex, pageSize, "where Title like @Title", "CreatedDate desc", new { Title = likeKeyword });
            }
            return result;
        }

        public IEnumerable<Album> GetListPagedItems(int pageIndex, int pageSize, out int totalCount)
        {          

            IEnumerable<Album> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                totalCount = _connection.RecordCount<Album>();
                result = _connection.GetListPaged<Album>(pageIndex, pageSize, null, "CreatedDate desc");
            }
            return result;
        }
    }

}
